{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 项目背景\n",
    "> 找出10个成绩最为相似的学生，即各科成绩相差的绝对值最小的10个同学。比如:\n",
    "   > - 两个学生的成绩为Student1(83,83,83,83,83,83,84);Student2(83,83,83,83,83,83,84)\n",
    "   > - Distance(Student1，Student2)=|83-83|+|83-83|+|83-83|+|83-83|+|83-83|+|84-84|=0;\n",
    "   > - 则判断这两个学生的成绩最为接近。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 解题思路1：笛卡尔积+相似度计算\n",
    "\n",
    "- 1.使用笛卡尔积的方式，得到每个学生和另外所有学生的关联行\n",
    "> 每个学生都要与其他所有行进行交叉关联，比如有100个学生，那应该得到100*100 = 1W行结果\n",
    "\n",
    "- 2.对于关联行，使用df.apply(function)的方法，计算两两相似度；\n",
    "> 本步骤是为了计算每个学生和其他学生的 相似度\n",
    "\n",
    "- 3.使用groupby + top n的方式，计算每个学生成绩最相近的10个学生"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>C语言程序设计</th>\n",
       "      <th>线性代数</th>\n",
       "      <th>大学英语</th>\n",
       "      <th>高等数学</th>\n",
       "      <th>管理学原理</th>\n",
       "      <th>大学计算机</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>学生2</td>\n",
       "      <td>85</td>\n",
       "      <td>95</td>\n",
       "      <td>75</td>\n",
       "      <td>95</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>学生3</td>\n",
       "      <td>78</td>\n",
       "      <td>84</td>\n",
       "      <td>77</td>\n",
       "      <td>94</td>\n",
       "      <td>87</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学生4</td>\n",
       "      <td>73</td>\n",
       "      <td>84</td>\n",
       "      <td>82</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>学生5</td>\n",
       "      <td>84</td>\n",
       "      <td>94</td>\n",
       "      <td>83</td>\n",
       "      <td>97</td>\n",
       "      <td>77</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  C语言程序设计  线性代数  大学英语  高等数学  管理学原理  大学计算机\n",
       "0  学生1       86    92    71    89     89     85\n",
       "1  学生2       85    95    75    95     82     83\n",
       "2  学生3       78    84    77    94     87     81\n",
       "3  学生4       73    84    82    95     87     72\n",
       "4  学生5       84    94    83    97     77     80"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel('data/成绩.xlsx', sheet_name='100_6', engine=\"openpyxl\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100, 7)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 笛卡尔积"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>C语言程序设计</th>\n",
       "      <th>线性代数</th>\n",
       "      <th>大学英语</th>\n",
       "      <th>高等数学</th>\n",
       "      <th>管理学原理</th>\n",
       "      <th>大学计算机</th>\n",
       "      <th>one</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>学生2</td>\n",
       "      <td>85</td>\n",
       "      <td>95</td>\n",
       "      <td>75</td>\n",
       "      <td>95</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>学生3</td>\n",
       "      <td>78</td>\n",
       "      <td>84</td>\n",
       "      <td>77</td>\n",
       "      <td>94</td>\n",
       "      <td>87</td>\n",
       "      <td>81</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学生4</td>\n",
       "      <td>73</td>\n",
       "      <td>84</td>\n",
       "      <td>82</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>学生5</td>\n",
       "      <td>84</td>\n",
       "      <td>94</td>\n",
       "      <td>83</td>\n",
       "      <td>97</td>\n",
       "      <td>77</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  C语言程序设计  线性代数  大学英语  高等数学  管理学原理  大学计算机  one\n",
       "0  学生1       86    92    71    89     89     85    1\n",
       "1  学生2       85    95    75    95     82     83    1\n",
       "2  学生3       78    84    77    94     87     81    1\n",
       "3  学生4       73    84    82    95     87     72    1\n",
       "4  学生5       84    94    83    97     77     80    1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['one'] = 1\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>C语言程序设计_x</th>\n",
       "      <th>线性代数_x</th>\n",
       "      <th>大学英语_x</th>\n",
       "      <th>高等数学_x</th>\n",
       "      <th>管理学原理_x</th>\n",
       "      <th>大学计算机_x</th>\n",
       "      <th>one</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>C语言程序设计_y</th>\n",
       "      <th>线性代数_y</th>\n",
       "      <th>大学英语_y</th>\n",
       "      <th>高等数学_y</th>\n",
       "      <th>管理学原理_y</th>\n",
       "      <th>大学计算机_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生2</td>\n",
       "      <td>85</td>\n",
       "      <td>95</td>\n",
       "      <td>75</td>\n",
       "      <td>95</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生3</td>\n",
       "      <td>78</td>\n",
       "      <td>84</td>\n",
       "      <td>77</td>\n",
       "      <td>94</td>\n",
       "      <td>87</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生4</td>\n",
       "      <td>73</td>\n",
       "      <td>84</td>\n",
       "      <td>82</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生5</td>\n",
       "      <td>84</td>\n",
       "      <td>94</td>\n",
       "      <td>83</td>\n",
       "      <td>97</td>\n",
       "      <td>77</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名_x  C语言程序设计_x  线性代数_x  大学英语_x  高等数学_x  管理学原理_x  大学计算机_x  one 姓名_y  \\\n",
       "0  学生1         86      92      71      89       89       85    1  学生1   \n",
       "1  学生1         86      92      71      89       89       85    1  学生2   \n",
       "2  学生1         86      92      71      89       89       85    1  学生3   \n",
       "3  学生1         86      92      71      89       89       85    1  学生4   \n",
       "4  学生1         86      92      71      89       89       85    1  学生5   \n",
       "\n",
       "   C语言程序设计_y  线性代数_y  大学英语_y  高等数学_y  管理学原理_y  大学计算机_y  \n",
       "0         86      92      71      89       89       85  \n",
       "1         85      95      75      95       82       83  \n",
       "2         78      84      77      94       87       81  \n",
       "3         73      84      82      95       87       72  \n",
       "4         84      94      83      97       77       80  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge = pd.merge(left=df, right=df, on='one')\n",
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 15)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 两两相似度计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['C语言程序设计', '线性代数', '大学英语', '高等数学', '管理学原理', '大学计算机']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = df.columns.tolist()\n",
    "columns.remove('姓名')\n",
    "columns.remove('one')\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sim_func(row):\n",
    "    sim_value = 0.0\n",
    "    for col in columns:\n",
    "        sim_value += abs(int(row[col + '_x']) - int(row[col + '_y']))\n",
    "    return sim_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge['sim'] = df_merge.apply(sim_func, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>C语言程序设计_x</th>\n",
       "      <th>线性代数_x</th>\n",
       "      <th>大学英语_x</th>\n",
       "      <th>高等数学_x</th>\n",
       "      <th>管理学原理_x</th>\n",
       "      <th>大学计算机_x</th>\n",
       "      <th>one</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>C语言程序设计_y</th>\n",
       "      <th>线性代数_y</th>\n",
       "      <th>大学英语_y</th>\n",
       "      <th>高等数学_y</th>\n",
       "      <th>管理学原理_y</th>\n",
       "      <th>大学计算机_y</th>\n",
       "      <th>sim</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生2</td>\n",
       "      <td>85</td>\n",
       "      <td>95</td>\n",
       "      <td>75</td>\n",
       "      <td>95</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生3</td>\n",
       "      <td>78</td>\n",
       "      <td>84</td>\n",
       "      <td>77</td>\n",
       "      <td>94</td>\n",
       "      <td>87</td>\n",
       "      <td>81</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生4</td>\n",
       "      <td>73</td>\n",
       "      <td>84</td>\n",
       "      <td>82</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>72</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生5</td>\n",
       "      <td>84</td>\n",
       "      <td>94</td>\n",
       "      <td>83</td>\n",
       "      <td>97</td>\n",
       "      <td>77</td>\n",
       "      <td>80</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名_x  C语言程序设计_x  线性代数_x  大学英语_x  高等数学_x  管理学原理_x  大学计算机_x  one 姓名_y  \\\n",
       "0  学生1         86      92      71      89       89       85    1  学生1   \n",
       "1  学生1         86      92      71      89       89       85    1  学生2   \n",
       "2  学生1         86      92      71      89       89       85    1  学生3   \n",
       "3  学生1         86      92      71      89       89       85    1  学生4   \n",
       "4  学生1         86      92      71      89       89       85    1  学生5   \n",
       "\n",
       "   C语言程序设计_y  线性代数_y  大学英语_y  高等数学_y  管理学原理_y  大学计算机_y   sim  \n",
       "0         86      92      71      89       89       85   0.0  \n",
       "1         85      95      75      95       82       83  23.0  \n",
       "2         78      84      77      94       87       81  33.0  \n",
       "3         73      84      82      95       87       72  53.0  \n",
       "4         84      94      83      97       77       80  41.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 把每个学生自己和自己关联的行去掉\n",
    "df_merge = df_merge[df_merge['姓名_x'] != df_merge['姓名_y']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>C语言程序设计_x</th>\n",
       "      <th>线性代数_x</th>\n",
       "      <th>大学英语_x</th>\n",
       "      <th>高等数学_x</th>\n",
       "      <th>管理学原理_x</th>\n",
       "      <th>大学计算机_x</th>\n",
       "      <th>one</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>C语言程序设计_y</th>\n",
       "      <th>线性代数_y</th>\n",
       "      <th>大学英语_y</th>\n",
       "      <th>高等数学_y</th>\n",
       "      <th>管理学原理_y</th>\n",
       "      <th>大学计算机_y</th>\n",
       "      <th>sim</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生2</td>\n",
       "      <td>85</td>\n",
       "      <td>95</td>\n",
       "      <td>75</td>\n",
       "      <td>95</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生3</td>\n",
       "      <td>78</td>\n",
       "      <td>84</td>\n",
       "      <td>77</td>\n",
       "      <td>94</td>\n",
       "      <td>87</td>\n",
       "      <td>81</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生4</td>\n",
       "      <td>73</td>\n",
       "      <td>84</td>\n",
       "      <td>82</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>72</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生5</td>\n",
       "      <td>84</td>\n",
       "      <td>94</td>\n",
       "      <td>83</td>\n",
       "      <td>97</td>\n",
       "      <td>77</td>\n",
       "      <td>80</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>学生1</td>\n",
       "      <td>86</td>\n",
       "      <td>92</td>\n",
       "      <td>71</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>学生6</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>66</td>\n",
       "      <td>98</td>\n",
       "      <td>83</td>\n",
       "      <td>72</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名_x  C语言程序设计_x  线性代数_x  大学英语_x  高等数学_x  管理学原理_x  大学计算机_x  one 姓名_y  \\\n",
       "1  学生1         86      92      71      89       89       85    1  学生2   \n",
       "2  学生1         86      92      71      89       89       85    1  学生3   \n",
       "3  学生1         86      92      71      89       89       85    1  学生4   \n",
       "4  学生1         86      92      71      89       89       85    1  学生5   \n",
       "5  学生1         86      92      71      89       89       85    1  学生6   \n",
       "\n",
       "   C语言程序设计_y  线性代数_y  大学英语_y  高等数学_y  管理学原理_y  大学计算机_y   sim  \n",
       "1         85      95      75      95       82       83  23.0  \n",
       "2         78      84      77      94       87       81  33.0  \n",
       "3         73      84      82      95       87       72  53.0  \n",
       "4         84      94      83      97       77       80  41.0  \n",
       "5         85      85      66      98       83       72  41.0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算每个学生成绩最相近的十个学生"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_student(df_sub):\n",
    "    df_sort = df_sub.sort_values(by='sim', ascending=True).head(10)\n",
    "    names = ','.join(list(df_sort['姓名_y']))\n",
    "    sims = ','.join(str(x) for x in df_sort['sim'].tolist())\n",
    "    return pd.Series({'names': names, 'sims': sims})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>names</th>\n",
       "      <th>sims</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>姓名_x</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>学生1</th>\n",
       "      <td>学生8,学生2,学生3,学生11,学生21,学生5,学生6,学生20,学生10,学生19</td>\n",
       "      <td>18.0,23.0,33.0,38.0,40.0,41.0,41.0,43.0,44.0,45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生10</th>\n",
       "      <td>学生26,学生2,学生6,学生18,学生28,学生5,学生30,学生12,学生34,学生13</td>\n",
       "      <td>19.0,29.0,29.0,38.0,38.0,39.0,39.0,39.0,40.0,41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生100</th>\n",
       "      <td>学生31,学生36,学生38,学生60,学生68,学生53,学生42,学生66,学生37,学生77</td>\n",
       "      <td>36.0,37.0,38.0,42.0,43.0,43.0,43.0,43.0,44.0,44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生11</th>\n",
       "      <td>学生20,学生23,学生3,学生39,学生9,学生1,学生8,学生40,学生24,学生2</td>\n",
       "      <td>33.0,35.0,37.0,37.0,37.0,38.0,38.0,39.0,39.0,41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生12</th>\n",
       "      <td>学生21,学生13,学生6,学生24,学生4,学生18,学生28,学生33,学生5,学生3</td>\n",
       "      <td>25.0,26.0,28.0,32.0,32.0,33.0,33.0,34.0,34.0,34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生95</th>\n",
       "      <td>学生93,学生94,学生96,学生97,学生89,学生92,学生91,学生84,学生88,学生80</td>\n",
       "      <td>43.0,54.0,55.0,56.0,61.0,63.0,64.0,71.0,72.0,73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生96</th>\n",
       "      <td>学生93,学生94,学生95,学生92,学生91,学生87,学生89,学生88,学生97,学生90</td>\n",
       "      <td>44.0,53.0,55.0,56.0,57.0,64.0,64.0,65.0,69.0,71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生97</th>\n",
       "      <td>学生81,学生83,学生89,学生93,学生94,学生91,学生72,学生80,学生92,学生95</td>\n",
       "      <td>36.0,37.0,41.0,45.0,46.0,52.0,53.0,53.0,55.0,56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生98</th>\n",
       "      <td>学生42,学生69,学生55,学生53,学生58,学生31,学生41,学生29,学生37,学生47</td>\n",
       "      <td>45.0,47.0,47.0,51.0,51.0,52.0,52.0,53.0,54.0,54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学生99</th>\n",
       "      <td>学生100,学生70,学生49,学生90,学生51,学生39,学生76,学生26,学生77,...</td>\n",
       "      <td>67.0,73.0,75.0,77.0,78.0,80.0,82.0,84.0,85.0,87.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   names  \\\n",
       "姓名_x                                                       \n",
       "学生1         学生8,学生2,学生3,学生11,学生21,学生5,学生6,学生20,学生10,学生19   \n",
       "学生10      学生26,学生2,学生6,学生18,学生28,学生5,学生30,学生12,学生34,学生13   \n",
       "学生100  学生31,学生36,学生38,学生60,学生68,学生53,学生42,学生66,学生37,学生77   \n",
       "学生11        学生20,学生23,学生3,学生39,学生9,学生1,学生8,学生40,学生24,学生2   \n",
       "学生12       学生21,学生13,学生6,学生24,学生4,学生18,学生28,学生33,学生5,学生3   \n",
       "...                                                  ...   \n",
       "学生95   学生93,学生94,学生96,学生97,学生89,学生92,学生91,学生84,学生88,学生80   \n",
       "学生96   学生93,学生94,学生95,学生92,学生91,学生87,学生89,学生88,学生97,学生90   \n",
       "学生97   学生81,学生83,学生89,学生93,学生94,学生91,学生72,学生80,学生92,学生95   \n",
       "学生98   学生42,学生69,学生55,学生53,学生58,学生31,学生41,学生29,学生37,学生47   \n",
       "学生99   学生100,学生70,学生49,学生90,学生51,学生39,学生76,学生26,学生77,...   \n",
       "\n",
       "                                                    sims  \n",
       "姓名_x                                                      \n",
       "学生1    18.0,23.0,33.0,38.0,40.0,41.0,41.0,43.0,44.0,45.0  \n",
       "学生10   19.0,29.0,29.0,38.0,38.0,39.0,39.0,39.0,40.0,41.0  \n",
       "学生100  36.0,37.0,38.0,42.0,43.0,43.0,43.0,43.0,44.0,44.0  \n",
       "学生11   33.0,35.0,37.0,37.0,37.0,38.0,38.0,39.0,39.0,41.0  \n",
       "学生12   25.0,26.0,28.0,32.0,32.0,33.0,33.0,34.0,34.0,34.0  \n",
       "...                                                  ...  \n",
       "学生95   43.0,54.0,55.0,56.0,61.0,63.0,64.0,71.0,72.0,73.0  \n",
       "学生96   44.0,53.0,55.0,56.0,57.0,64.0,64.0,65.0,69.0,71.0  \n",
       "学生97   36.0,37.0,41.0,45.0,46.0,52.0,53.0,53.0,55.0,56.0  \n",
       "学生98   45.0,47.0,47.0,51.0,51.0,52.0,52.0,53.0,54.0,54.0  \n",
       "学生99   67.0,73.0,75.0,77.0,78.0,80.0,82.0,84.0,85.0,87.0  \n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_result = df_merge.groupby(by='姓名_x').apply(get_top_student)\n",
    "df_result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 解题思路2：双重for循环+最小堆解决需求\n",
    "堆是数据结构中最常见的一种数据结构，是一个完全二叉树。最小堆中每一个节点的值都小于等于其子树中每个节点的值。\n",
    "\n",
    "具体的原理，学过数据结构的读者都懂，没有学过的也不用深究。\n",
    "\n",
    "上面的代码中使用最小堆的目的是为了避免排序，采用堆化方式查找N个最小值，基于最小堆的查找，时间复杂度在O(logN)~O(N)之间，\n",
    "\n",
    "远比排序的时间复杂度O(nlogN)快。\n",
    "\n",
    "关于heapq的官方文档：https://docs./zh-cn/3/library/heapq.html?highlight=heapq#module-heapq\n",
    "\n",
    "heapq的实现源码：https://github.com/python/cpython/blob/3.9/Lib/heapq.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import heapq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = df.values\n",
    "length = data.shape[0]\n",
    "names = data[:, 0]\n",
    "scores = data[:, 1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = []\n",
    "# 遍历取出每一行的数据\n",
    "for i in range(length):\n",
    "    # 分别取出当前遍历出来的姓名和成绩列表\n",
    "    name, score = names[i], scores[i]\n",
    "    # 用一个最小堆保存最接近的10学生\n",
    "    min_similar_10 = []\n",
    "    for j in range(length):\n",
    "        if i == j:\n",
    "            # 跳过对自己的比较\n",
    "            continue\n",
    "        # 被比较的学生姓名和成绩\n",
    "        find_name, find_scores = names[j], scores[j]\n",
    "        # 计算两个学生成绩的距离\n",
    "        sim_value = np.abs(score-find_scores).sum()\n",
    "        # 将被比较的学生和对应距离保存到最小堆中\n",
    "        heapq.heappush(min_similar_10, (find_name, sim_value))\n",
    "    # 取出10个距离最小的学生\n",
    "    min_similar_10 = heapq.nsmallest(10, min_similar_10, key=lambda x: x[1])\n",
    "    name_distance_dict = dict(min_similar_10)\n",
    "    name_similars, distances = list(name_distance_dict.keys()), list(name_distance_dict.values())\n",
    "    result.append((name, name_similars, distances))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>分数最接近的10个学生</th>\n",
       "      <th>距离</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>学生1</td>\n",
       "      <td>[学生8, 学生2, 学生3, 学生11, 学生21, 学生5, 学生6, 学生20, 学生...</td>\n",
       "      <td>[18, 23, 33, 38, 40, 41, 41, 43, 44, 45]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>学生2</td>\n",
       "      <td>[学生8, 学生5, 学生1, 学生3, 学生10, 学生7, 学生6, 学生13, 学生1...</td>\n",
       "      <td>[19, 20, 23, 28, 29, 31, 34, 36, 36, 37]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>学生3</td>\n",
       "      <td>[学生4, 学生15, 学生7, 学生13, 学生2, 学生18, 学生23, 学生17, ...</td>\n",
       "      <td>[20, 27, 27, 28, 28, 29, 30, 31, 31, 32]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学生4</td>\n",
       "      <td>[学生13, 学生3, 学生15, 学生29, 学生18, 学生17, 学生12, 学生24...</td>\n",
       "      <td>[20, 20, 21, 24, 25, 27, 32, 32, 33, 36]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>学生5</td>\n",
       "      <td>[学生2, 学生7, 学生13, 学生8, 学生14, 学生12, 学生3, 学生24, 学...</td>\n",
       "      <td>[20, 21, 28, 31, 32, 34, 36, 38, 38, 39]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>学生96</td>\n",
       "      <td>[学生93, 学生94, 学生95, 学生92, 学生91, 学生87, 学生89, 学生8...</td>\n",
       "      <td>[44, 53, 55, 56, 57, 64, 64, 65, 69, 71]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>学生97</td>\n",
       "      <td>[学生81, 学生83, 学生89, 学生93, 学生94, 学生91, 学生72, 学生8...</td>\n",
       "      <td>[36, 37, 41, 45, 46, 52, 53, 53, 55, 56]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>学生98</td>\n",
       "      <td>[学生42, 学生55, 学生69, 学生53, 学生58, 学生31, 学生41, 学生2...</td>\n",
       "      <td>[45, 47, 47, 51, 51, 52, 52, 53, 54, 54]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>学生99</td>\n",
       "      <td>[学生100, 学生70, 学生49, 学生90, 学生51, 学生39, 学生76, 学生...</td>\n",
       "      <td>[67, 73, 75, 77, 78, 80, 82, 84, 85, 87]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>学生100</td>\n",
       "      <td>[学生31, 学生36, 学生38, 学生60, 学生42, 学生53, 学生66, 学生6...</td>\n",
       "      <td>[36, 37, 38, 42, 43, 43, 43, 43, 44, 44]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       姓名                                        分数最接近的10个学生  \\\n",
       "0     学生1  [学生8, 学生2, 学生3, 学生11, 学生21, 学生5, 学生6, 学生20, 学生...   \n",
       "1     学生2  [学生8, 学生5, 学生1, 学生3, 学生10, 学生7, 学生6, 学生13, 学生1...   \n",
       "2     学生3  [学生4, 学生15, 学生7, 学生13, 学生2, 学生18, 学生23, 学生17, ...   \n",
       "3     学生4  [学生13, 学生3, 学生15, 学生29, 学生18, 学生17, 学生12, 学生24...   \n",
       "4     学生5  [学生2, 学生7, 学生13, 学生8, 学生14, 学生12, 学生3, 学生24, 学...   \n",
       "..    ...                                                ...   \n",
       "95   学生96  [学生93, 学生94, 学生95, 学生92, 学生91, 学生87, 学生89, 学生8...   \n",
       "96   学生97  [学生81, 学生83, 学生89, 学生93, 学生94, 学生91, 学生72, 学生8...   \n",
       "97   学生98  [学生42, 学生55, 学生69, 学生53, 学生58, 学生31, 学生41, 学生2...   \n",
       "98   学生99  [学生100, 学生70, 学生49, 学生90, 学生51, 学生39, 学生76, 学生...   \n",
       "99  学生100  [学生31, 学生36, 学生38, 学生60, 学生42, 学生53, 学生66, 学生6...   \n",
       "\n",
       "                                          距离  \n",
       "0   [18, 23, 33, 38, 40, 41, 41, 43, 44, 45]  \n",
       "1   [19, 20, 23, 28, 29, 31, 34, 36, 36, 37]  \n",
       "2   [20, 27, 27, 28, 28, 29, 30, 31, 31, 32]  \n",
       "3   [20, 20, 21, 24, 25, 27, 32, 32, 33, 36]  \n",
       "4   [20, 21, 28, 31, 32, 34, 36, 38, 38, 39]  \n",
       "..                                       ...  \n",
       "95  [44, 53, 55, 56, 57, 64, 64, 65, 69, 71]  \n",
       "96  [36, 37, 41, 45, 46, 52, 53, 53, 55, 56]  \n",
       "97  [45, 47, 47, 51, 51, 52, 52, 53, 54, 54]  \n",
       "98  [67, 73, 75, 77, 78, 80, 82, 84, 85, 87]  \n",
       "99  [36, 37, 38, 42, 43, 43, 43, 43, 44, 44]  \n",
       "\n",
       "[100 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.DataFrame(result, columns=[\"姓名\", \"分数最接近的10个学生\", \"距离\"])\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 解题思路3：numpy向量化操作\n",
    "\n",
    "利用numpy广播变量的特性一次性求出该学生与所有学生（含自己）的距离："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = []\n",
    "for i in range(length):\n",
    "    name, score = names[i], scores[i]\n",
    "    # 利用numpy广播变量的特性一次性求出该学生与所有学生（含自己）的距离\n",
    "    score_diff = np.abs(scores-score).sum(axis=1)\n",
    "    min_similar_index = np.argpartition(score_diff, 11)[:11].tolist()\n",
    "    min_similar_index.remove(i)\n",
    "    name_similars = names[min_similar_index]\n",
    "    distances = score_diff[min_similar_index]\n",
    "    result.append((name, name_similars, distances))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>分数最接近的10个学生</th>\n",
       "      <th>距离</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>学生1</td>\n",
       "      <td>[学生8, 学生2, 学生3, 学生11, 学生21, 学生5, 学生6, 学生20, 学生...</td>\n",
       "      <td>[18, 23, 33, 38, 40, 41, 41, 43, 44, 45]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>学生2</td>\n",
       "      <td>[学生8, 学生5, 学生1, 学生3, 学生10, 学生7, 学生6, 学生13, 学生1...</td>\n",
       "      <td>[19, 20, 23, 28, 29, 31, 34, 36, 36, 37]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>学生3</td>\n",
       "      <td>[学生4, 学生7, 学生15, 学生2, 学生13, 学生18, 学生23, 学生25, ...</td>\n",
       "      <td>[20, 27, 27, 28, 28, 29, 30, 31, 31, 32]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学生4</td>\n",
       "      <td>[学生3, 学生13, 学生15, 学生29, 学生18, 学生17, 学生24, 学生12...</td>\n",
       "      <td>[20, 20, 21, 24, 25, 27, 32, 32, 33, 36]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>学生5</td>\n",
       "      <td>[学生2, 学生7, 学生13, 学生8, 学生14, 学生12, 学生3, 学生24, 学...</td>\n",
       "      <td>[20, 21, 28, 31, 32, 34, 36, 38, 38, 39]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>学生96</td>\n",
       "      <td>[学生93, 学生94, 学生95, 学生92, 学生91, 学生87, 学生89, 学生8...</td>\n",
       "      <td>[44, 53, 55, 56, 57, 64, 64, 65, 69, 71]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>学生97</td>\n",
       "      <td>[学生81, 学生83, 学生89, 学生93, 学生94, 学生91, 学生72, 学生8...</td>\n",
       "      <td>[36, 37, 41, 45, 46, 52, 53, 53, 55, 56]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>学生98</td>\n",
       "      <td>[学生42, 学生55, 学生69, 学生53, 学生58, 学生31, 学生41, 学生2...</td>\n",
       "      <td>[45, 47, 47, 51, 51, 52, 52, 53, 54, 54]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>学生99</td>\n",
       "      <td>[学生100, 学生70, 学生49, 学生90, 学生51, 学生39, 学生76, 学生...</td>\n",
       "      <td>[67, 73, 75, 77, 78, 80, 82, 84, 85, 87]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>学生100</td>\n",
       "      <td>[学生31, 学生36, 学生38, 学生60, 学生53, 学生66, 学生68, 学生4...</td>\n",
       "      <td>[36, 37, 38, 42, 43, 43, 43, 43, 44, 44]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       姓名                                        分数最接近的10个学生  \\\n",
       "0     学生1  [学生8, 学生2, 学生3, 学生11, 学生21, 学生5, 学生6, 学生20, 学生...   \n",
       "1     学生2  [学生8, 学生5, 学生1, 学生3, 学生10, 学生7, 学生6, 学生13, 学生1...   \n",
       "2     学生3  [学生4, 学生7, 学生15, 学生2, 学生13, 学生18, 学生23, 学生25, ...   \n",
       "3     学生4  [学生3, 学生13, 学生15, 学生29, 学生18, 学生17, 学生24, 学生12...   \n",
       "4     学生5  [学生2, 学生7, 学生13, 学生8, 学生14, 学生12, 学生3, 学生24, 学...   \n",
       "..    ...                                                ...   \n",
       "95   学生96  [学生93, 学生94, 学生95, 学生92, 学生91, 学生87, 学生89, 学生8...   \n",
       "96   学生97  [学生81, 学生83, 学生89, 学生93, 学生94, 学生91, 学生72, 学生8...   \n",
       "97   学生98  [学生42, 学生55, 学生69, 学生53, 学生58, 学生31, 学生41, 学生2...   \n",
       "98   学生99  [学生100, 学生70, 学生49, 学生90, 学生51, 学生39, 学生76, 学生...   \n",
       "99  学生100  [学生31, 学生36, 学生38, 学生60, 学生53, 学生66, 学生68, 学生4...   \n",
       "\n",
       "                                          距离  \n",
       "0   [18, 23, 33, 38, 40, 41, 41, 43, 44, 45]  \n",
       "1   [19, 20, 23, 28, 29, 31, 34, 36, 36, 37]  \n",
       "2   [20, 27, 27, 28, 28, 29, 30, 31, 31, 32]  \n",
       "3   [20, 20, 21, 24, 25, 27, 32, 32, 33, 36]  \n",
       "4   [20, 21, 28, 31, 32, 34, 36, 38, 38, 39]  \n",
       "..                                       ...  \n",
       "95  [44, 53, 55, 56, 57, 64, 64, 65, 69, 71]  \n",
       "96  [36, 37, 41, 45, 46, 52, 53, 53, 55, 56]  \n",
       "97  [45, 47, 47, 51, 51, 52, 52, 53, 54, 54]  \n",
       "98  [67, 73, 75, 77, 78, 80, 82, 84, 85, 87]  \n",
       "99  [36, 37, 38, 42, 43, 43, 43, 43, 44, 44]  \n",
       "\n",
       "[100 rows x 3 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.DataFrame(result, columns=[\"姓名\", \"分数最接近的10个学生\", \"距离\"])\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 解题思路4：KNN算法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.neighbors import KNeighborsClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 取出用于被KNN训练的数据\n",
    "data = df.iloc[:, 1:].values\n",
    "# y本身用于标注每条数据属于哪个类别，但我并不使用KNN的分类功能，所以统一全部标注为类别0\n",
    "y = np.zeros(data.shape[0], dtype='int8')\n",
    "\n",
    "knn = KNeighborsClassifier(n_neighbors=1, algorithm='ball_tree', p=1)\n",
    "knn.fit(data, y)\n",
    "distance, similar_points = knn.kneighbors(data, n_neighbors=11, return_distance=True)\n",
    "distance = distance.astype(\"int\", copy=False)\n",
    "names = df['姓名']\n",
    "result = []\n",
    "for i, name in names.iteritems():\n",
    "    name_similar_indexs = similar_points[i].tolist()\n",
    "    self_index = name_similar_indexs.index(i)\n",
    "    name_similar_indexs.pop(self_index)\n",
    "    name_similars = names[name_similar_indexs].tolist()\n",
    "    distances = distance[i].tolist()\n",
    "    distances.pop(self_index)\n",
    "    result.append((name, name_similars, distances))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>分数最接近的10个学生</th>\n",
       "      <th>距离</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>学生1</td>\n",
       "      <td>[学生8, 学生2, 学生3, 学生11, 学生21, 学生6, 学生5, 学生20, 学生...</td>\n",
       "      <td>[18, 23, 33, 38, 40, 41, 41, 43, 44, 45]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>学生2</td>\n",
       "      <td>[学生8, 学生5, 学生1, 学生3, 学生10, 学生7, 学生6, 学生13, 学生1...</td>\n",
       "      <td>[19, 20, 23, 28, 29, 31, 34, 36, 36, 37]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>学生3</td>\n",
       "      <td>[学生4, 学生15, 学生7, 学生2, 学生13, 学生18, 学生23, 学生25, ...</td>\n",
       "      <td>[20, 27, 27, 28, 28, 29, 30, 31, 31, 32]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>学生4</td>\n",
       "      <td>[学生3, 学生13, 学生15, 学生29, 学生18, 学生17, 学生12, 学生24...</td>\n",
       "      <td>[20, 20, 21, 24, 25, 27, 32, 32, 33, 36]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>学生5</td>\n",
       "      <td>[学生2, 学生7, 学生13, 学生8, 学生14, 学生12, 学生3, 学生24, 学...</td>\n",
       "      <td>[20, 21, 28, 31, 32, 34, 36, 38, 38, 39]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>学生96</td>\n",
       "      <td>[学生93, 学生94, 学生95, 学生92, 学生91, 学生87, 学生89, 学生8...</td>\n",
       "      <td>[44, 53, 55, 56, 57, 64, 64, 65, 69, 71]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>学生97</td>\n",
       "      <td>[学生81, 学生83, 学生89, 学生93, 学生94, 学生91, 学生72, 学生8...</td>\n",
       "      <td>[36, 37, 41, 45, 46, 52, 53, 53, 55, 56]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>学生98</td>\n",
       "      <td>[学生42, 学生55, 学生69, 学生58, 学生53, 学生31, 学生41, 学生2...</td>\n",
       "      <td>[45, 47, 47, 51, 51, 52, 52, 53, 54, 54]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>学生99</td>\n",
       "      <td>[学生100, 学生70, 学生49, 学生90, 学生51, 学生39, 学生76, 学生...</td>\n",
       "      <td>[67, 73, 75, 77, 78, 80, 82, 84, 85, 87]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>学生100</td>\n",
       "      <td>[学生31, 学生36, 学生38, 学生60, 学生53, 学生68, 学生42, 学生6...</td>\n",
       "      <td>[36, 37, 38, 42, 43, 43, 43, 43, 44, 44]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       姓名                                        分数最接近的10个学生  \\\n",
       "0     学生1  [学生8, 学生2, 学生3, 学生11, 学生21, 学生6, 学生5, 学生20, 学生...   \n",
       "1     学生2  [学生8, 学生5, 学生1, 学生3, 学生10, 学生7, 学生6, 学生13, 学生1...   \n",
       "2     学生3  [学生4, 学生15, 学生7, 学生2, 学生13, 学生18, 学生23, 学生25, ...   \n",
       "3     学生4  [学生3, 学生13, 学生15, 学生29, 学生18, 学生17, 学生12, 学生24...   \n",
       "4     学生5  [学生2, 学生7, 学生13, 学生8, 学生14, 学生12, 学生3, 学生24, 学...   \n",
       "..    ...                                                ...   \n",
       "95   学生96  [学生93, 学生94, 学生95, 学生92, 学生91, 学生87, 学生89, 学生8...   \n",
       "96   学生97  [学生81, 学生83, 学生89, 学生93, 学生94, 学生91, 学生72, 学生8...   \n",
       "97   学生98  [学生42, 学生55, 学生69, 学生58, 学生53, 学生31, 学生41, 学生2...   \n",
       "98   学生99  [学生100, 学生70, 学生49, 学生90, 学生51, 学生39, 学生76, 学生...   \n",
       "99  学生100  [学生31, 学生36, 学生38, 学生60, 学生53, 学生68, 学生42, 学生6...   \n",
       "\n",
       "                                          距离  \n",
       "0   [18, 23, 33, 38, 40, 41, 41, 43, 44, 45]  \n",
       "1   [19, 20, 23, 28, 29, 31, 34, 36, 36, 37]  \n",
       "2   [20, 27, 27, 28, 28, 29, 30, 31, 31, 32]  \n",
       "3   [20, 20, 21, 24, 25, 27, 32, 32, 33, 36]  \n",
       "4   [20, 21, 28, 31, 32, 34, 36, 38, 38, 39]  \n",
       "..                                       ...  \n",
       "95  [44, 53, 55, 56, 57, 64, 64, 65, 69, 71]  \n",
       "96  [36, 37, 41, 45, 46, 52, 53, 53, 55, 56]  \n",
       "97  [45, 47, 47, 51, 51, 52, 52, 53, 54, 54]  \n",
       "98  [67, 73, 75, 77, 78, 80, 82, 84, 85, 87]  \n",
       "99  [36, 37, 38, 42, 43, 43, 43, 43, 44, 44]  \n",
       "\n",
       "[100 rows x 3 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.DataFrame(result, columns=[\"姓名\", \"分数最接近的10个学生\", \"距离\"])\n",
    "result"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "237.867px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
